package tbg.dispense;

import com.google.common.collect.Maps;
import net.tt.logger.FileHelper;
import net.tt.logger.Log;
import net.tt.util.CastUtil;
import net.tt.util.ExcelFileHelper;
import net.tt.util.Lists;
import net.tt.util.Objs;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import tbg.util.MonthEnum;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * 修改为无状态<br>
 * 整理后数据分发-先处理单个文件操作，任务：
 * 1. 数据调整；
 * 2. sheet页名称、删除多余页；
 * 3. 比对上月字段，调整顺序
 * ----------------------------------->
 * 1. 处理顺序：
 * 2. 数据调整；
 * 3. 列排列；
 * 4. 行排序；---------------
 * 5. 格式调整：左对齐、添加边框
 * ---------------------------------
 * F:\\Users\\tbc\\Desktop\\济南重汽2016年11月.xlsx
 * F:\\Users\\tbc\\Desktop\\济南重汽.xlsx
 *
 * @author tbc on 2016/11/26 23:42.
 */
public class DataDispense2 {
    public static void main(String[] args) throws IOException {
        XSSFWorkbook lastMonthWB;
        XSSFWorkbook thisMonthWB;

        List<String> preColumns = Lists.newArrayList();
        long preCount = 0;

        System.out.println("拖入上月文件：");
        Scanner scanner = new Scanner(System.in);
        String lastMonthPath = scanner.nextLine();
        System.out.println("加载上个月excel文件...");
        lastMonthWB = ExcelFileHelper.getXSSFWorkbook(lastMonthPath);
        System.out.println("获取上个月数据Sheet页...");
        XSSFSheet lastMonthSheet = getDataSheet(lastMonthWB);
        System.out.println("获取上月标题列表...");
        List<String> preColumn = proLastMonthData(lastMonthSheet);


        System.out.println("拖入本月文件：");
        String thisMonthPath = scanner.nextLine();
        System.out.println("加载本月excel文件...");
        thisMonthWB = ExcelFileHelper.getXSSFWorkbook(thisMonthPath);
        System.out.println("获取本月数据Sheet页...");
        XSSFSheet thisMonthSheet = getDataSheet(thisMonthWB);

        System.out.println("开始进行数据调整...");
        dataAdjust(lastMonthSheet, thisMonthSheet);

        System.out.println("开始进行字段调整...");
        columnAdjust(thisMonthSheet, preColumns);

        System.out.println("删除多余Sheet页...");
        deleteRedundantSheet(thisMonthWB);

        //排序

        // 格式：边框
        setStyle(thisMonthWB);

        System.out.println("正在生成新的EXCEL文件...");
        write(thisMonthPath, thisMonthWB);
    }

    private static void setStyle(XSSFWorkbook wb) {
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow title = sheet.getRow(0);
        CellRangeAddress c = new CellRangeAddress(0, sheet.getLastRowNum(), 0, title.getLastCellNum());
        // 边框
//        RegionUtil.setBorderTop(1, c, sheet, thisMonthWB);
//        RegionUtil.setBorderBottom(1, c, sheet, thisMonthWB);
//        RegionUtil.setBorderLeft(1, c, sheet, thisMonthWB);
//        RegionUtil.setBorderRight(1, c, sheet, thisMonthWB);

        // 左对齐
        XSSFCellStyle style = wb.createCellStyle();
        style.setBorderTop((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);

        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);


    }

    /**
     * 删除多余的Sheet页
     *
     * @param wb wb
     */
    private static void deleteRedundantSheet(XSSFWorkbook wb) {
        Arrays.stream(new String[]{"Sheet1", "Sheet2", "Sheet3", "deleteCopy"}).forEach(name -> deleteSheet(wb, name));
    }

    private static void deleteSheet(XSSFWorkbook wb, String sheetName) {
        XSSFSheet sheet = wb.getSheet(sheetName);
        int index = wb.getSheetIndex(sheet);
        wb.removeSheetAt(index);
    }

    /**
     * 设置上月标题列表及总数量
     */
    private static List<String> proLastMonthData(XSSFSheet sheet) {
        // 数量所在的列序号
        Map<String, Integer> amountColumnIndexMap = Maps.newHashMap();

        List<String> preColumns = Lists.newArrayList();

        // 第一行：标题行
        XSSFRow row = sheet.getRow(0);
        row.forEach(cell -> {
            String content = cell.getStringCellValue();
            preColumns.add(cell.getStringCellValue());
            if ("数量".equals(content) || "销量".equals(content))
                amountColumnIndexMap.put("amount", cell.getColumnIndex());
        });

        int amount = 0;
        int amountIndex = amountColumnIndexMap.get("amount");
        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            XSSFCell cell = sheet.getRow(i).getCell(amountIndex);
            amount += CastUtil.castInt(cell.getRawValue());
        }
        System.out.println("--> 上月数据标题行：" + preColumns);
        return preColumns;
    }

    /**
     * 获取sheet页中数量（或销量）数据
     *
     * @param sheet s
     * @return s
     */
    private static long getAmount(XSSFSheet sheet) {
        int amountIndex = 0;
        long count = 0;
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            if (i == 0) {
                XSSFRow firstRow = sheet.getRow(i);
                for (int j = 0; j < firstRow.getLastCellNum(); j++) {
                    XSSFCell cell = firstRow.getCell(j);
                    if ("数量".equals(cell.getStringCellValue()) || "销量".equals(cell.getStringCellValue())) {
                        amountIndex = cell.getColumnIndex();
                        break;
                    }
                }
            }
            XSSFCell cell = sheet.getRow(i).getCell(amountIndex);
            count += CastUtil.castInt(cell.getRawValue());
        }
        return count;
    }

    /**
     * 调整数据
     *
     * @param lastMonthSheet .
     * @param thisMonthSheet .
     */
    private static void dataAdjust(XSSFSheet lastMonthSheet, XSSFSheet thisMonthSheet) {
        Scanner scanner = new Scanner(System.in);
        int adjustCount = 0, rowCount = 0;

        long preCount = getAmount(lastMonthSheet);
        long thisCount = getAmount(thisMonthSheet);
        // 输出数量
        System.out.println("上月数量：" + preCount + "- 本月数量：" + thisCount);

        // 随机生成加减类型: true is +; false is -;
        boolean adjustType = isAdd();
        // 随机生成加减的数量
        adjustCount = randomCount(thisCount);
        System.out.println("系统推荐调整数量 [" + adjustCount + "] （同意推荐值直接回车，不同意则输入要调整数量）：");
        String _count = scanner.nextLine();
        if (Objs.nonEmpty(_count)) adjustCount = CastUtil.castInt(_count);
        // 要调整的行数
        rowCount = (int) (adjustCount / 2);
        System.out.println("系统推荐调整行数 [" + rowCount + "] （同意推荐值直接回车，不同意则输入要调整行数）：");
        String _rowCount = scanner.nextLine();
        System.out.println(_rowCount + " ----------------------");
        if (Objs.nonEmpty(_rowCount)) rowCount = CastUtil.castInt(_rowCount);
        // 随机生成行号列表
        List<Integer> adjustRowsIndex = getProRows(rowCount, thisMonthSheet);

        // 开始调整，得到调整后的Sheet
        adjust(adjustRowsIndex, thisMonthSheet, adjustCount, adjustType);
        System.out.println("调整完成，开始删除空行...");
        // 删除空行
        deleteRowByZero(thisMonthSheet);

    }

    public static void deleteRowByZero(XSSFSheet thisMonthSheet) {
        int amountIndex = getAmountIndex(thisMonthSheet);
        int i = thisMonthSheet.getLastRowNum();
        XSSFRow row;
        while (i > 1) {
            i--;
            row = thisMonthSheet.getRow(i);
            XSSFCell cell = row.getCell(amountIndex);
//            System.out.println("...cell raw : " + cell.getRawValue());
            if (cell.getNumericCellValue() == 0.0) {
                // 这个方法可以把startRow至endRow之间的所有行，按照n向上或移动。
                // n如果为正数，则整体区域向下移动，
                // n如果为负数，则整体区域向上移动，
                // 我们如果要删除空行，就可以利用shiftRows方法，从sheet表的最后一行开始向上逆序移动非空行。
//                System.out.println("删除第" + i + "行 ");
                // sheet.removeRow(row);
                thisMonthSheet.shiftRows(i + 1, thisMonthSheet.getLastRowNum(), -1);
            }
        }
        System.out.println("删除完成，开始复制一个名为'deleteCopy'的Sheet页...");
        copySheet(thisMonthSheet);
    }

    private static XSSFSheet copySheet(XSSFSheet sheet) {
//        String sheetName = MonthEnum.getCn(CastUtil.castInt(LocalDate.now().getMonthValue()));
        String sheetName = "deleteCopy";

        XSSFWorkbook thisMonthWB = sheet.getWorkbook();
        XSSFSheet _sheet = thisMonthWB.createSheet(sheetName);

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFRow _row = _sheet.createRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                XSSFCell cell = row.getCell(j);
                XSSFCell _cell = _row.createCell(j);
                if (cell == null) {
                    _cell.setCellValue("");
                } else {
                    setNewCellValue(_cell, cell);
//                    if (cell.getCellType() == 1) {
//                        _cell.setCellValue(cell.getStringCellValue());
//                    } else if (cell.getCellType() == 0) {
//                        // 数值类型
//                        _cell.setCellValue(toDouble(cell.getRawValue()));
//                    } else {
//                        _cell.setCellValue(cell.getRawValue());
//                    }
                }
            }
        }
        System.out.println("复制完成...");
        return _sheet;
    }

    private static void adjust(List<Integer> rowsIndex, XSSFSheet thisMonthSheet, int adjustCount, boolean isAdd) {
        // 定义一个变量，记录已经增加的总数量
        int temp = 0;

        // 数量所在的列序号
        int amountIndex = getAmountIndex(thisMonthSheet);

        for (Integer i : rowsIndex) {
            // 这一行要调整
            XSSFRow row = thisMonthSheet.getRow(i);
            // 调整的值为1-3随机
            int random = new Random().nextInt(3) + 1;
            XSSFCell cell = row.getCell(amountIndex);
            String cellRawValue = cell.getRawValue();
            Log.info("cellRawValue = " + cellRawValue);
            if (cellRawValue.endsWith(".0")) {
                cellRawValue = cellRawValue.substring(0, cellRawValue.length() - 2);
                Log.info("update cellRawValue = " + cellRawValue);
            }
            int value = CastUtil.castInt(cellRawValue);

            if (isAdd) {
                cell.setCellValue(value + random);
            } else {
                cell.setCellValue(value - random);
            }
            temp += random;
            if (temp >= adjustCount) break;
        }

        if (temp < adjustCount) {
            adjustCount -= temp;
            adjust(rowsIndex, thisMonthSheet, adjustCount, isAdd);
        }
    }

    /**
     * 数量所在的列序号
     *
     * @param thisMonthSheet sheet
     * @return 序号
     */
    private static int getAmountIndex(XSSFSheet thisMonthSheet) {
        // 数量所在的列序号
        Map<String, Integer> amountColumnIndexMap = Maps.newHashMap();

        // 第一行：标题行
        XSSFRow row = thisMonthSheet.getRow(0);
        for (Cell cell : row) {
            String content = cell.getStringCellValue();
            if ("数量".equals(content) || "销量".equals(content))
                return cell.getColumnIndex();
        }
        return 0;
    }

    private static List<Integer> getProRows(int rowCount, XSSFSheet thisMonthSheet) {
        // 随机足够数量的记录，标号缓存在List
        List<Integer> rowsIndex = new ArrayList<>();
        for (int i = 0; i < rowCount; i++) {
            int lastRow = thisMonthSheet.getLastRowNum();
            while (true) {
                int t = new Random().nextInt(lastRow) + 1;
                if (!rowsIndex.contains(t)) {
                    rowsIndex.add(t);
                    break;
                }
            }
            if (rowsIndex.size() == rowCount)
                break;
        }
        return rowsIndex;
    }

    /**
     * 调整字段
     *
     * @param thisMonth 存放当月数据的sheet页
     */
    private static void columnAdjust(XSSFSheet thisMonth, List<String> preColumns) {
        // 所谓本月，其实才是真的上个月。。。都是整理前一个月的数据
        String newSheetName = MonthEnum.getCn(CastUtil.castInt(LocalDate.now().minusMonths(1).getMonthValue()));
        // 上月列顺序
        System.out.println("上月列顺序: " + preColumns);

        XSSFRow oldFirstRow = thisMonth.getRow(0);
        XSSFWorkbook thisMonthWB = thisMonth.getWorkbook();

        // 序号1，一会儿把0删除
        XSSFSheet newSheet = thisMonthWB.createSheet(newSheetName);
        // 创建新Sheet页标题行
        XSSFRow newFirstRow = newSheet.createRow(0);
        // 设置新Sheet页标题行内容
        for (int i = 0; i < preColumns.size(); i++) {
            XSSFCell newTitleCell = newFirstRow.createCell(i);
            newTitleCell.setCellValue(preColumns.get(i));
        }

        // 在这儿把排序做了
//        List<XSSFRow> rowList = Lists.newArrayList();
//        thisMonth.forEach(row -> {
//            if (row.getRowNum() == 0) return;
//            rowList.add((XSSFRow) row);
//        });
//
//        Ordering.from((XSSFRow r1, XSSFRow r2) -> {
//            int r0c = r1.getCell(0).getRawValue().compareTo(r2.getCell(0).getRawValue());
//            if (r1.getCell(0).getRawValue().compareTo(r2.getCell(0).getRawValue())>0){
//                return 1;
//            }else if ()
//        })

        // 调整本月列表顺序
        for (int i = 1; i <= thisMonth.getLastRowNum(); i++) {
            XSSFRow oldRow = thisMonth.getRow(i);
            XSSFRow newRow = newSheet.createRow(i);
//            for (int j = 0; j < preColumns.size(); j++) {
//                String title = preColumns.get(j);
//                for (Cell oldCell : oldRow) {
//                    String oldTitle = oldFirstRow.getCell(oldCell.getColumnIndex()).getStringCellValue();
//                    if (oldTitle.equals(title)) {
//                        XSSFCell newCell = newRow.createCell(j);
//                        setNewCellValue(newCell, (XSSFCell) oldCell);
//                        break;
//                    }
//                }
//            }
            for (int j = 0; j < preColumns.size(); j++) {
                XSSFCell newCell = null;
                String title = preColumns.get(j);
                for (Cell oldCell : oldRow) {
                    String oldTitle = oldFirstRow.getCell(oldCell.getColumnIndex()).getStringCellValue();
                    if (oldTitle.equals(title)) {
                        newCell = newRow.createCell(j);
                        setNewCellValue(newCell, (XSSFCell) oldCell);
                        break;
                    }
                }
                // 如果本月中找不到相应的字段,怎么处理
                if (newCell == null) {
                    System.out.println("找不着字段： " + title);
                    int n = 5;
                    for (int m = 0; i < n; m++) {
                        System.out.println("系统将于" + (n - m) + "秒后退出...\n");
                        try {
                            TimeUnit.SECONDS.sleep(1);
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                    }
                    System.exit(0);
                }
            }
        }
        System.out.println("字段调整完成 : " + newSheet);

    }

    /**
     * 按类型设置新的Cell的值
     * <li>Cell#CELL_TYPE_NUMERIC - 0
     * <li>Cell#CELL_TYPE_STRING - 1
     * <li>Cell#CELL_TYPE_FORMULA - 2
     * <li>Cell#CELL_TYPE_BLANK - 3
     * <li>Cell#CELL_TYPE_BOOLEAN - 4
     * <li>Cell#CELL_TYPE_ERROR - 5
     *
     * @param newCell c
     * @param oldCell c
     */
    private static void setNewCellValue(XSSFCell newCell, XSSFCell oldCell) {
        if (oldCell.getCellType() == 0) {
            newCell.setCellValue(oldCell.getNumericCellValue());
        } else if (oldCell.getCellType() == 1) {
            newCell.setCellValue("null".equals(oldCell.getStringCellValue()) || Objs.isEmpty(oldCell.getStringCellValue()) ? "" : oldCell.getStringCellValue());
        } else if (oldCell.getCellType() == 2) {
            newCell.setCellValue(oldCell.getCellFormula());
        } else if (oldCell.getCellType() == 3) {
            newCell.setCellValue("");
        } else if (oldCell.getCellType() == 4) {
            newCell.setCellValue(oldCell.getBooleanCellValue());
        } else if (oldCell.getCellType() == 5) {
            newCell.setCellValue(oldCell.getErrorCellValue());
        } else {
            newCell.setCellValue(oldCell.getRawValue());
        }

    }

    /**
     * 获取Sheet页
     */
    private static XSSFSheet getDataSheet(XSSFWorkbook wb) {
        return wb.getSheetAt(0);
    }

    /**
     * 随机确定加还是减
     *
     * @return boolean
     */
    private static boolean isAdd() {
        return new Random().nextBoolean();
    }

    /**
     * 随机产生要调整的数量
     *
     * @return count
     */
    private static int randomCount(long rowCount) {
        if (rowCount <= 100) {
            return 0;
        } else if (rowCount <= 300) {
            return getRandom(15);
        } else if (rowCount <= 500) {
            return getRandom(25);
        } else if (rowCount <= 700) {
            return getRandom(35);
        } else if (rowCount <= 900) {
            return getRandom(45);
        } else if (rowCount <= 1500) {
            return getRandom(55);
        } else if (rowCount <= 2500) {
            return getRandom(65);
        } else if (rowCount <= 4000) {
            return getRandom(75);
        } else if (rowCount <= 10000) {
            return getRandom(95, 20);
        } else {
            // > 10000
            return getRandom(140, 30);
        }
    }

    private static int getRandom(int value) {
        return getRandom(value, 10);
    }

    private static int getRandom(int value, int floating) {
        return new Random().nextInt(floating) + value;
    }


    private static int getYear() {
        return LocalDate.now().getYear();
    }

    private static int getMonth() {
        return LocalDate.now().minusMonths(1).getMonthValue();
    }


    /**
     * 输出到新的文件
     */
    private static void write(String thisMonthPath, XSSFWorkbook thisMonthWB) throws IOException {
        // 文件目录下是否有pro文件夹
        File file = FileHelper.loadFile(thisMonthPath);
        File parent = file.getParentFile();
        File[] files = parent.listFiles();
        assert files != null;
        Optional<File> optional = Arrays.stream(files).filter(f -> f.getName().equals("pro")).findFirst();
        if (!optional.isPresent()) {
            // 创建文件夹pro
            File pro = new File(parent.getPath() + "\\pro");
            boolean isSuc = pro.mkdir();
            Log.info("创建文件夹pro " + isSuc);
        }


        // 设置文件：原文件path + '/pro' + $原文件名 + $年 + '年' + $月 + '月' + '.xlsx'
        String[] ps = thisMonthPath.split("\\\\");
        String oldName = ps[ps.length - 1];
        String newFileName = "";
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < ps.length - 1; i++) {
            sb.append(ps[i]).append("\\\\");
        }
        sb.append("pro\\\\").append(oldName.substring(0, oldName.length() - 5))
                .append(getYear())
                .append("年")
                .append(getMonth())
                .append("月")
                .append(".xlsx");
        newFileName = sb.toString();
        thisMonthWB.write(new FileOutputStream(newFileName));
    }
}
